package com.zkx.method;

import com.zkx.db.DButil;
import com.zkx.model.Goddess;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class GoddessMethod {
    public void insertGoddess(Goddess g) throws SQLException {
        Connection conn = DButil.getConnection();
        Statement st = conn.createStatement();
        String sql ="insert into `imooc_goddess` " +
                "(`user_name`,`sex`,`age`,`birthday`,`email`,`mobile`,`create_user`,`create_date`,`update_user`,`update_date`,`isdel`)" +
                "values (?,?,?,?,?,?,?,current_date(),?,current_date(),?)";
        //预编译sql语句
        PreparedStatement ptmt = conn.prepareStatement(sql);
        //赋值
        ptmt.setString(1,g.getUser_name());
        ptmt.setInt(2,g.getSex());
        ptmt.setInt(3,g.getAge());
        ptmt.setDate(4,new Date(g.getBirthday().getTime()));
        ptmt.setString(5,g.getEmail());
        ptmt.setString(6,g.getMobile());
        ptmt.setString(7,g.getCreate_user());
        ptmt.setString(8,g.getUpdate_user());
        ptmt.setInt(9,g.getIsdel());
        //开始执行
        ptmt.execute();
    }
    public void updateGoddess(Goddess g) throws SQLException {
        Connection conn = DButil.getConnection();
        Statement st = conn.createStatement();
        String sql ="update `imooc_goddess` set" +
                "`user_name`=?,`sex`=?,`age`=?,`birthday`=?,`email`=?,`mobile`=?,`update_user`=?,`update_date`=current_date(),`isdel`=?" +
                " where `id`=? ";//此处where前面要有空格
        //预编译sql语句
        PreparedStatement ptmt = conn.prepareStatement(sql);
        //赋值
        ptmt.setString(1,g.getUser_name());
        ptmt.setInt(2,g.getSex());
        ptmt.setInt(3,g.getAge());
        ptmt.setDate(4,new Date(g.getBirthday().getTime()));
        ptmt.setString(5,g.getEmail());
        ptmt.setString(6,g.getMobile());
        ptmt.setString(7,g.getUpdate_user());
        ptmt.setInt(8,g.getIsdel());
        ptmt.setInt(9,g.getId());
        //开始执行
        ptmt.execute();
    }
    public void deleteGoddess(Integer id) throws SQLException {
        Connection conn = DButil.getConnection();
        Statement st = conn.createStatement();
        String sql = "delete from `imooc_goddess` where `id`=?";
        //预编译sql语句
        PreparedStatement ptmt = conn.prepareStatement(sql);
        //赋值
        ptmt.setInt(1,id);
        //开始执行
        ptmt.execute();
    }
    public List<Goddess> selectGoddess() throws SQLException {
        Connection conn = DButil.getConnection();
        Statement st = conn.createStatement();
        ResultSet rs = st.executeQuery("select `id`,`user_name` from `imooc_goddess`");
        List<Goddess> gs = new ArrayList<Goddess>();
        Goddess g = null;
        while (rs.next()){
            g = new Goddess();
            g.setId(rs.getInt("id"));
            g.setUser_name(rs.getString("user_name"));
            //将对象加入list
            gs.add(g);
        }
        return gs;
    }
    //得到某个人的详细信息
    public Goddess selectGoddess1(Integer id) throws SQLException {
        Goddess g = null;
        Connection conn = DButil.getConnection();
        Statement st = conn.createStatement();
        String sql = "select * from `imooc_goddess` where `id`=?";
        //预编译sql语句
        PreparedStatement ptmt = conn.prepareStatement(sql);
        //赋值
        ptmt.setInt(1,id);
        //开始执行
        ResultSet rs = ptmt.executeQuery();
        while(rs.next()){
            g = new Goddess();
            g.setId(rs.getInt("id"));
            g.setUser_name(rs.getString("user_name"));
            g.setSex(rs.getInt("sex"));
            g.setAge(rs.getInt("age"));
            g.setBirthday(rs.getDate("birthday"));
            g.setEmail(rs.getString("email"));
            g.setMobile(rs.getString("mobile"));
            g.setCreate_user(rs.getString("create_user"));
            g.setCreate_date(rs.getDate("create_date"));
            g.setUpdate_user(rs.getString("update_user"));
            g.setUpdate_date(rs.getDate("update_date"));
            g.setIsdel(rs.getInt("isdel"));
        }return g;
    }

    //使用姓名查询某个人详细信息
    public Goddess selectGoddess(String name) throws SQLException {
        Connection conn = DButil.getConnection();
        Statement st = conn.createStatement();
        String sql = "select * from `imooc_goddess` where `user_name` = ?";
        PreparedStatement ptmt = conn.prepareStatement(sql);
        ptmt.setString(1,name);
        ResultSet rs = ptmt.executeQuery();
        Goddess g = null;
        while (rs.next()){
            g = new Goddess();
            g.setId(rs.getInt("id"));
            g.setUser_name(rs.getString("user_name"));
            g.setSex(rs.getInt("sex"));
            g.setAge(rs.getInt("age"));
            g.setBirthday(rs.getDate("birthday"));
            g.setEmail(rs.getString("email"));
            g.setMobile(rs.getString("mobile"));
            g.setCreate_user(rs.getString("create_user"));
            g.setCreate_date(rs.getDate("create_date"));
            g.setUpdate_user(rs.getString("update_user"));
            g.setUpdate_date(rs.getDate("update_date"));
            g.setIsdel(rs.getInt("isdel"));
        }
        return g;
    }


    public List<Goddess> selectGoddess(List<Map<String,Object>> params) throws SQLException {
        List<Goddess> result = new ArrayList<Goddess>();
        Connection conn = DButil.getConnection();
        Statement st = conn.createStatement();
        StringBuilder sb = new StringBuilder();
        sb.append("select * from `imooc_goddess` where 1=1");
        if(params!= null && params.size() > 0){
            for(int i = 0;i<params.size();i++){
                Map<String, Object> map = params.get(i);
                sb.append(" and "+map.get("name")+" "+map.get("rela")+" "+map.get("value"));
            }
        }
        System.out.println(sb.toString());
        PreparedStatement ptmt = conn.prepareStatement(sb.toString());

        ResultSet rs = ptmt.executeQuery();
        Goddess g = null;
        while (rs.next()){
            g = new Goddess();
            g.setId(rs.getInt("id"));
            g.setUser_name(rs.getString("user_name"));
            g.setSex(rs.getInt("sex"));
            g.setAge(rs.getInt("age"));
            g.setBirthday(rs.getDate("birthday"));
            g.setEmail(rs.getString("email"));
            g.setMobile(rs.getString("mobile"));
            g.setCreate_user(rs.getString("create_user"));
            g.setCreate_date(rs.getDate("create_date"));
            g.setUpdate_user(rs.getString("update_user"));
            g.setUpdate_date(rs.getDate("update_date"));
            g.setIsdel(rs.getInt("isdel"));
            result.add(g);
        }
        return result;
    }
}
